package edu.nctu.csie.jichang.database.save;

import java.io.File;
import java.sql.Statement;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import edu.nctu.csie.jichang.database.dbinfo.LoginInfo;
import edu.nctu.csie.jichang.database.model.cell.AbstractRootElement;
import edu.nctu.csie.jichang.database.model.cell.DBDatabase;
import edu.nctu.csie.jichang.database.model.cell.DBRow;
import edu.nctu.csie.jichang.database.model.cell.DBSchema;
import edu.nctu.csie.jichang.database.model.cell.DBTable;
import edu.nctu.csie.jichang.database.model.cell.DBTableHeader;
import edu.nctu.csie.jichang.database.model.connection.IDBConnection;


/**
 * SaveSQLDB
 * @author Jichang
 */
public class SaveSQLDB implements ISave {
	
	private static final Logger LOG = LoggerFactory.getLogger(SaveSQLDB.class);
	
	/**
	 * (non-Javadoc)
	 * @see edu.nctu.csie.jichang.database.save.ISave#doSave(java.util.LinkedHashMap, com.jichang.dbtools.model.cell.DBDatabase)
	 */
	public void doSave(List<LoginInfo> pConns, DBDatabase pDatabase) throws Exception {
//		IDBConnection tConn = null;
//		for(String tName:pConns.keySet()){
//			try{
//				tConn = pConns.get(tName);
//				if(tConn == null)
//					continue;
//				LogSystem.getInstance().log("Starting  - ".intern() + tName +"'s Schema");
//				// �]�w transaction
//				tConn.setAutoCommit(false);
//				// �R�����
//				tConn.cleanAllTablesData();
//				// �s�W���
//				if(pDatabase.hasSchema(tName)){
//					saveSchema(pDatabase.getSchema(tName),tConn);
//				}
//				// Commit
//				tConn.commit();
//				tConn.close();
//				LogSystem.getInstance().log("Finishing - ".intern() + tName +"'s Schema");
//			}catch (Exception e) {
//				// Rollback
//				tConn.rollback();
//				LogSystem.getInstance().log(e);
//			}
//		}
	}

	public void saveSchema(DBSchema pMySchema,IDBConnection pConn) throws Exception {
		for(String s:pConn.getInsertOrder()){
			LOG.info("Insert Data To "+s+" Table");
			if(pMySchema.hasTable(s)){
				saveTable(pMySchema.getTable(s),pConn);
			}
		}
	}
	
	/**
	 * �N Table �s��
	 * @param pTable Table
	 * @param pType �s������
	 * @throws Exception if Error
	 */
	public void saveTable(DBTable pTable, IDBConnection pConn) throws Exception {
		if(pTable.getCount()==0)
			return;
		Statement tStat = pConn.getStatement();
		for(String i:pTable.getRowNames()){
			String tSQL = null;
			try{
				tSQL = saveRow(pTable.getTableName(),pTable.getTableHeader(),pTable.getRow(i)).toString();
				LOG.info(tSQL);
//				tStat.executeUpdate(tSQL);
			}catch (Exception e) {
				tStat.close();
			}
		}
		tStat.close();
	}
	
	public StringBuffer saveRow(String pTableName, DBTableHeader pTableHeader,DBRow pIRow) throws Exception {
		StringBuffer tSQL = new StringBuffer();
		StringBuffer tValue = new StringBuffer();
		tSQL.append("Insert Into ").append(pTableName).append(" ( ");
		for (String s:pTableHeader.getColumnNames()){
			tSQL.append(s).append(",");
			if(pIRow.hasCell(s)){
				tValue.append(getRightType(pTableHeader.getColumnType(s),pIRow.getCell(s).getName())).append(",");
			}else{
				tValue.append("null").append(",");
			}
		}
		tSQL.deleteCharAt(tSQL.length()-1);
		tValue.deleteCharAt(tValue.length()-1);
		tSQL.append(" ) Values ( ").append(tValue).append(" ) ");
		return tSQL;
	}
	

	/**
	 * ��o���T����X�榡
	 * @param pType type
	 * @param pValue Value
	 * @return Value
	 * @throws Exception if Error
	 */
	public String getRightType(AbstractRootElement<?> pType, String pValue) throws Exception{
//		if (pType instanceof ColumnInfo) {
//			ColumnInfo tColumnInfo = (ColumnInfo) pType;
//			switch(tColumnInfo.getColumnType()){
//				case String:
//					return "'"+pValue.replaceAll("'", "''")+"'";
//				case Integer:
//					return pValue;
//				case Date:
//					return "to_date('"+pValue+"','YYYY/MM/DD HH24:MI:SS')";
//				case Binary:
//					return pValue;
//			}
//		}
		return null;
	}


	/**
	 * (non-Javadoc)
	 * @see edu.nctu.csie.jichang.database.save.ISave#doSave(java.io.File, com.jichang.dbtools.model.cell.DBDatabase)
	 */
	public void doSave(File pFile, DBDatabase pDatabase) throws Exception {
		throw new Exception("The Object not support this function");
	}
	
}
